-- 充值记录
CREATE TABLE withdraws (
    id SERIAL NOT NULL,
    bill_no CHAR(20) NOT NULL DEFAULT '',               -- 订单号
    date DATE NOT NULL DEFAULT CURRENT_DATE,            -- 日期
    user_id INT NOT NULL,                               -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',          -- 用户名称
    amount DECIMAL(16, 4) NOT NULL DEFAULT 0,           -- 支付金额
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 添加时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 修改时间
    status SMALLINT NOT NULL DEFAULT 0,                 -- 状态, 0: 待审核; 1: 已审待出款; 2: 已出款/提现成功; 3: 冻结; 4: 拒绝;
    audit_admin_id INT NOT NULL,                        -- 审核管理员编号
    audit_admin_name VARCHAR(20) NOT NULL DEFAULT '',   -- 审核管理员名称
    audit_remark VARCHAR(200) NOT NULL DEFAULT '',      -- 审核备注
    audited TIMESTAMPTZ NOT NULL DEFAULT NOW(),       -- 审核时间
    reason VARCHAR(200) NOT NULL DEFAULT '',            -- 拒绝原因
    finished TIMESTAMPTZ,                               -- 完成时间/成功时间
    bank_id INT NOT NULL DEFAULT 0,                     -- 银行编号
    bank_name VARCHAR(50) NOT NULL DEFAULT '',          -- 银行名称
    real_name VARCHAR(20) NOT NULL DEFAULT '',          -- 真实姓名
    card_id INT NOT NULL DEFAULT 0,                     -- 用户银行卡编号
    card_number VARCHAR(20) NOT NULL DEFAULT '',        -- 用户卡号
    fee DECIMAL(16, 4) NOT NULL DEFAULT 0,              -- 手续费
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (audit_admin_id) REFERENCES admins(id),
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);

-- 索引
CREATE INDEX ON withdraws(date);
CREATE INDEX ON withdraws(created);

-- 分区/按月
CREATE TABLE withdraws_202005 PARTITION OF withdraws FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE withdraws_202006 PARTITION OF withdraws FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE withdraws_202007 PARTITION OF withdraws FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE withdraws_202008 PARTITION OF withdraws FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE withdraws_202009 PARTITION OF withdraws FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE withdraws_202010 PARTITION OF withdraws FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE withdraws_202011 PARTITION OF withdraws FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE withdraws_202012 PARTITION OF withdraws FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE withdraws_202101 PARTITION OF withdraws FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE withdraws_202102 PARTITION OF withdraws FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE withdraws_202103 PARTITION OF withdraws FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE withdraws_202104 PARTITION OF withdraws FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE withdraws_202105 PARTITION OF withdraws FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE withdraws_202106 PARTITION OF withdraws FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE withdraws_202107 PARTITION OF withdraws FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE withdraws_202108 PARTITION OF withdraws FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE withdraws_202109 PARTITION OF withdraws FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE withdraws_202110 PARTITION OF withdraws FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE withdraws_202111 PARTITION OF withdraws FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE withdraws_202112 PARTITION OF withdraws FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

--  触发器
--  修改时保存账变信息
CREATE OR REPLACE FUNCTION withdraw_update_trigger()
    RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.status < OLD.status THEN -- 不能将状态进行回退
        RAISE EXCEPTION '修改充值状态的值不能为 0';
    ELSEIF NEW.status = 2 THEN      -- 表示出款成功
        EXECUTE account_change_save(NEW.bill_no, NEW.user_id, 2, 2, NEW.amount);
    ELSEIF NEW.status = 4 THEN      -- 拒绝出款
        EXECUTE account_change_save(NEW.bill_no, NEW.user_id, 21, 32, NEW.amount);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER withdraw_update
    AFTER UPDATE ON withdraws
    FOR EACH ROW EXECUTE PROCEDURE withdraw_update_trigger();

-- 保存信息时, 状态值必须为0
CREATE OR REPLACE FUNCTION withdraw_create_trigger()
    RETURN TRIGGER
AS $$
    BEGIN
        -- 插入数据时此值必须为0
        -- 如果状态值为其他将导致账变出现误差
        IF NEW.status <> 0 THEN
            RAISE EXCEPTION '充值状态的值不能非0';
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER withdraw_create
    BEFORE INSERT ON withdraws
    FOR EACH ROW EXECUTE PROCEDURE withdraw_create_trigger();

-- 申请提现
-- 执行此函数前, 需要保证用户有提款金额大于0
CREATE OR REPLACE FUNCTION apply_withdraw(p_bill_no VARCHAR, p_user_id INTEGER, p_amount DECIMAL, p_card_id INTEGER)
    RETURNS TEXT
AS $$
    DECLARE
        v_r user_accounts%ROWTYPE;
        v_bank banks%ROWTYPE;
        v_card user_cards%ROWTYPE;
    BEGIN
        -- 先检测用户在不在
        SELECT * INTO v_r WHERE user_id = p_user_id;
        IF NOT FOUND THEN
            RETURN '用户信息查找失败';
        END IF;
        -- 检测用户账户信息
        IF v_r.remain < p_amount THEN
            RETURN '用户余额不足';
        END IF;
        -- 获取用户卡信息
        SELECT * INTO v_card FROM user_cards WHERE user_id = p_user_id AND id = p_card_id;
        IF NOT FOUND THEN
            RETURN '用户的绑定银行卡无效';
        END IF;
        -- 获取银行相关信息
        SELECT * INTO v_bank FROM banks WHERE id = v_card.bank_id;
        IF NOT FOUND THEN
            RETURN '用户银行卡的银行信息无效';
        END IF;
        -- 冻结用户相应提款金额
        UPDATE user_accounts
            SET
                frozen = frozen + p_amount,
                remain = remain - p_amout
            WHERE
                uesr_id = p_user_id;
        -- 写提款申请信息
        INSERT INTO withdraws
                (bill_no, date, user_id, user_name, amount, bank_id, bank_name, card_id, card_number, real_name)
            VALUES
                (p_bill_no, CURRENT_DATE, v_r.user_id, v_r.user_name, p_amount, v_bank.id, v_bank.name, p_card_id, v_card.card_number, v_card.real_name);
        RETURN 'ok';
    END;
$$ LANGUAGE plpgsql;
